
# This is the server logic for a Shiny web application.
# You can find out more about building applications with Shiny here:
#
# http://shiny.rstudio.com
#

library(shiny)
library(DT)
library(RMySQL)
library(data.table) 
library(rmarkdown)

shinyServer(function(input, output) {
  
  options(DT.options = list(
    searchHighlight = TRUE,          
    language = list(
      info = '显示第_START_ 至 _END_ 项结果，共 _TOTAL_ 项',
      search = '搜索:',
      paginate = list(previous = '上页', `next` = '下页'),
      lengthMenu = '显示 _MENU_ 项结果')))
  
  
 sql<- "SELECT 
count(a.audio_id) as `sumCount`,
 b.book_id,
 b.name as `bookName`,
 u.name as `userName`,
 b.broadcaster, 
 ROUND(sum(a.audio_length) / 60 / 60,2) as `sumLength` ,
 b.payment_amount, 
 ROUND(b.payment_amount * sum(a.audio_length) / 60 / 60,2) as `sumPay` 
 FROM 
 `audios` as a,
 albums as b,
 users as u
 WHERE 
 a.album_id = b.album_id  and
 a.created_at >=  '2017-08-05 16:34:00' AND
 a.status =  1 AND
 u.user_id = a.user_id 
 group by  b.album_id,a.user_id"
 
 #dev conn <- dbConnect(MySQL(), dbname = "aplus-audio", username="homestead", password="secret", host="127.0.0.1", port=33060)
 #test 
 conn <- dbConnect(MySQL(), dbname = "aplus-audio", username="root", password="miying", host="127.0.0.1", port=3306)
 
  dbSendQuery(conn,'SET NAMES UTF8')
  
  rest<-dbSendQuery(conn,sql)
  data.frame = fetch(rest, n = -1)
  myData<-data.frame 
  
  setnames(myData,c('sumCount','book_id','bookName','userName','broadcaster','sumLength','payment_amount','sumPay'),
                 c('专辑音频数','专辑ID','专辑名','用户名','主播名','专辑总时长','录制单价','总价'))
  
  output$table <-DT::renderDataTable(DT::datatable({
    myData },rownames = FALSE))
  
  output$downloadData <- downloadHandler(
    filename = 'file-js.csv',
    content = function(file){
      fwrite(myData,file)
    })
})

